package com.alibaba.csp.sentinel.dashboard.repository.dao;

import javax.swing.table.DefaultTableModel;
import java.sql.*;


public class SqlHelper {
    private static DbConfig dbConfig;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    public static void setDbConfig(DbConfig config) {
        dbConfig = config;

    }

    public static Connection getConnection(boolean isRead) {
        try {
            DbConfig currentConfig = dbConfig;
            if (isRead) {
                currentConfig = dbConfig.getReadonly();
            }
            return DriverManager.getConnection(currentConfig.getUrl(), currentConfig.getUserName(), currentConfig.getPassword());
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public static PreparedStatement getPreparedStatement(Connection conn, String cmdText, Object... cmdParams) {
        if (conn == null) {
            return null;
        }

        PreparedStatement pstmt = null;
        try {
            pstmt = conn.prepareStatement(cmdText, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            int i = 1;
            for (Object item : cmdParams) {
                pstmt.setObject(i, item);
                i++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            closeConnection(pstmt);
        }
        return pstmt;
    }

    public static int ExecSql(String cmdText, Object... cmdParams) {
        PreparedStatement pstmt = getPreparedStatement(getConnection(false), cmdText, cmdParams);
        if (pstmt == null) {
            return -2;
        }
        int i;
        try {
            i = pstmt.executeUpdate();
        } catch (SQLException ex) {
            ex.printStackTrace();
            i = -1;
        }
        closeConnection(pstmt);
        return i;
    }

    public static DefaultTableModel getResultSet(String cmdText) {
        return getResultSet(cmdText, true);
    }

    public static DefaultTableModel getResultSet(String cmdText, boolean isRead) {
        Statement stmt = getStatement(isRead);
        if (stmt == null) {
            return null;
        }
        try {
            return buildTableModel(stmt.executeQuery(cmdText));
        } catch (SQLException ex) {
            ex.printStackTrace();
            closeConnection(stmt);
        }
        return null;
    }

    private static DefaultTableModel buildTableModel(ResultSet rs) {
        if (rs == null) {
            return null;
        }
        ResultSetMetaData rsm;
        String[] columnNames = null; // 列标题
        Object[][] data = null; // 数据项
        DefaultTableModel model; // 表格模型
        try {
            // 查询语句
            rsm = rs.getMetaData();
            // 判断时候可以实现对数据库的更新
            if (rs.getConcurrency() == ResultSet.CONCUR_UPDATABLE) {
                System.out.println("Can UPDATABLE");
            } else {
                System.out.println("Only Ready");
            }
            // 获取列标题
            columnNames = new String[rsm.getColumnCount()];
            for (int i = 0; i < rsm.getColumnCount(); i++) {
                columnNames[i] = rsm.getColumnName(i + 1);
            }

            int row = 0;
            int colum = 0;
            int columCount = rsm.getColumnCount();
            // 获取行数,没有直接的方法,这里先移动到纪录结尾,获取行号,即为行数,然后再移回来
            rs.last();
            int rowCount = rs.getRow();
            rs.beforeFirst();
            System.out.println("rowCount:" + rowCount);
            // 读取数据到数据项变量
            data = new Object[rowCount][columCount];
            while (rs.next()) {
                for (colum = 0; colum < rsm.getColumnCount(); colum++) {
                    data[row][colum] = rs.getObject(colum + 1);
                }
                row++;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }

        // 初始化数据模型
        model = new DefaultTableModel(data, columnNames) {

            /**
             * 重写 getColumnClass 可以使表格自动识别数据类型
             */
            @SuppressWarnings("unchecked")
            @Override
            public Class getColumnClass(int c) {
                // 这里要对空数据集进行检验
                if (dataVector.isEmpty() == false && getValueAt(0, c) != null) {
                    return getValueAt(0, c).getClass();
                } else {
                    return Object.class;
                }
            }
        };

        return model;
    }

    private static void closeConnection(Object obj) {
        if (obj == null) {
            return;
        }
        // close(obj);
        try {
            Connection connection = null;
            if (obj instanceof Statement) {
                connection = ((Statement) obj).getConnection();
            } else if (obj instanceof PreparedStatement) {
                connection = ((PreparedStatement) obj).getConnection();
            } else if (obj instanceof ResultSet) {
                connection = ((ResultSet) obj).getStatement().getConnection();
            } else if (obj instanceof Connection) {
                connection = ((Connection) obj);
            }

            if (connection != null && !connection.isClosed())
                connection.close();

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    private static void close(Object obj) {
        if (obj == null) {
            return;
        }
        try {
            if (obj instanceof Statement) {
                ((Statement) obj).close();
            } else if (obj instanceof PreparedStatement) {
                ((PreparedStatement) obj).close();
            } else if (obj instanceof ResultSet) {
                ((ResultSet) obj).close();
            } else if (obj instanceof Connection) {
                ((Connection) obj).close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static Statement getStatement(boolean isRead) {
        Connection conn = getConnection(isRead);
        if (conn == null) {
            return null;
        }
        try {
            return conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            // 设置数据集可以滚动,可以更新
        } catch (SQLException ex) {
            ex.printStackTrace();
            closeConnection(conn);
        }
        return null;
    }

}
